package com.wanbatv.wanba.ahitv.sqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.SystemClock;

import com.wanbatv.wanba.ahitv.data.MySportItem;


/**
 * Created by Forcs on 15/6/24.
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    /**
     * 数据库名称
     */
    private static final String DATABASE_NAME = "wanba.db";
    /**
     * 数据库版本号
     */
    private static final int DATABASE_VERSION = 2;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + DbUtils.MySport.TABLE + " ("
                + DbUtils.MySport._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + DbUtils.MySport.COL_ID + " INTEGER,"
                + DbUtils.MySport.COL_NAME + " TEXT,"
                + DbUtils.MySport.COL_TIME + " INTEGER"
                + ");");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DbUtils.MySport.TABLE + ";");
        db.execSQL("CREATE TABLE " + DbUtils.MySport.TABLE + " ("
                + DbUtils.MySport._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + DbUtils.MySport.COL_ID + " INTEGER,"
                + DbUtils.MySport.COL_NAME + " TEXT,"
                + DbUtils.MySport.COL_TIME + " INTEGER"
                + ");");
    }

    /**
     * 获取我的运动记录列表
     */
    public MySportItem[] getMySportList() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = null;
        MySportItem[] list = null;
        try {
            c = db.query(DbUtils.MySport.TABLE, new String[]{
                    DbUtils.MySport._ID, DbUtils.MySport.COL_ID,
                    DbUtils.MySport.COL_NAME, DbUtils.MySport.COL_TIME
            }, null, null, null, null, DbUtils.MySport.COL_TIME + " DESC");

            if (c != null && c.moveToFirst()) {
                final int count = c.getCount();
                if (count > 0) {
                    list = new MySportItem[count];
                    int index = 0;
                    do {
                        MySportItem item = new MySportItem();
                        item.id = c.getInt(c.getColumnIndexOrThrow(DbUtils.MySport.COL_ID));
                        item.name = c.getString(c.getColumnIndexOrThrow(DbUtils.MySport.COL_NAME));
                        item.time = c.getInt(c.getColumnIndexOrThrow(DbUtils.MySport.COL_TIME));
                        list[index] = item;
                        index++;
                    } while (c.moveToNext());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null) {
                c.close();
            }
        }
        return list;
    }

    /**
     * 添加新的我的运动记录
     * @param colId 栏目id
     * @param name 栏目名称
     * @return true 表示添加成功，否则失败
     */
    public boolean addMySportItem(int colId, String name) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor c = null;
        boolean result = false;
        //最大条数为4条
        final int MAX_COUNT = 4;

        try {
            boolean found = false;
            c = db.query(DbUtils.MySport.TABLE, new String[]{
                    DbUtils.MySport._ID, DbUtils.MySport.COL_ID
            }, null, null, null, null, null);
            if (c != null && c.moveToFirst() && c.getCount() > 0) {
                //先遍历表，判断是否需要插入数据
                //并记录表中栏目id，用于后面的删除操作
                final int count = c.getCount();
                int[] tempColId = new int[count];
                int index = 0;
                do {
                    int cId = c.getInt(c.getColumnIndexOrThrow(DbUtils.MySport.COL_ID));
                    if (cId == colId) {
                        found = true;
                    }
                    tempColId[index] = cId;
                    index++;
                } while (c.moveToNext());

                //现有的列表中不存在需要添加的栏目
                //则需要将栏目插入表中
                if (!found) {
                    ContentValues values = new ContentValues();
                    values.put(DbUtils.MySport.COL_ID, colId);
                    values.put(DbUtils.MySport.COL_NAME, name);
                    values.put(DbUtils.MySport.COL_TIME, SystemClock.uptimeMillis());
                    result = db.insert(DbUtils.MySport.TABLE, null, values) != -1;
                } else {  //否则跟新插入的时间点
                    ContentValues values = new ContentValues();
                    values.put(DbUtils.MySport.COL_ID, colId);
                    values.put(DbUtils.MySport.COL_NAME, name);
                    values.put(DbUtils.MySport.COL_TIME, SystemClock.uptimeMillis());
                    result = db.update(DbUtils.MySport.TABLE, values, DbUtils.MySport.COL_ID + "=" + colId, null) > 0;
                }

                //将多余的数据删除，如果需要的话
                if (count > MAX_COUNT) {
                    for (int i = MAX_COUNT; i < count; i++) {
                        int id = tempColId[i];
                        db.delete(DbUtils.MySport.TABLE, DbUtils.MySport.COL_ID + "=" + id, null);
                    }
                }
            } else {
                ContentValues values = new ContentValues();
                values.put(DbUtils.MySport.COL_ID, colId);
                values.put(DbUtils.MySport.COL_NAME, name);
                values.put(DbUtils.MySport.COL_TIME, SystemClock.uptimeMillis());
                result = db.insert(DbUtils.MySport.TABLE, null, values) != -1;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null) {
                c.close();
            }
        }

        return result;
    }
}
